import base64
import datetime
import json
import logging

from utils import db_util
from controller import base_handler


class ListHandler(base_handler.BaseHandler):
    def get(self):
        page = self.get_query_argument("page", "1")
        perPage = self.get_query_argument("perPage", "10")
        orderBy = self.get_query_argument("orderBy", "sys_code")
        orderDir = self.get_query_argument("orderDir", "asc")

        params = []
        where_sql = "where"
        for fieldName in ("sys_code", "sys_name", "sys_leader", "sys_desc", "sys_status"):
            field_value = self.get_query_argument(fieldName, None)
            if field_value:
                params.append("%" + field_value + "%")
                where_sql += " %s like ? and" % fieldName
        sql = """select * from crud_system """
        if len(params) > 0:
            sql += where_sql[0:-3]
        else:
            params = None
        sql += " order by %s %s" % (orderBy, orderDir)
        rows = db_util.execute_page(sql, int(page), int(perPage), params)
        for row in rows["items"]:
            system_id = row["id"]
            sql = """select count(*) from crud_module  where system_id=? """
            tmp_row = db_util.fetchone(sql, [system_id])
            row["module_count"] = tmp_row[0]
            sql = """select count(*) from crud_resource  where system_id=? """
            tmp_row = db_util.fetchone(sql, [system_id])
            row["resource_count"] = tmp_row[0]
            sql = """select count(*) from crud_interface  where system_id=? """
            tmp_row = db_util.fetchone(sql, [system_id])
            row["interface_count"] = tmp_row[0]
        res = {"status": 0, "message": "ok",
               "data": {"page": page, "total": rows["total"], "rows": rows["items"]}}

        self.set_response(res, 200)


class SelectHandler(base_handler.BaseHandler):
    def get(self):
        sql = """select distinct id,sys_name,sys_code from crud_system  where sys_status=1 order by sys_code"""
        rows = db_util.fetchall(sql)
        options = []
        for row in rows:
            options.append({"value": row[0], "label": row[1], "sys_code": row[2]})
        res = {"status": 0, "message": "ok", "data": {"options": options}}
        self.set_response(res, 200)


class TreeHandler(base_handler.BaseHandler):
    @staticmethod
    def resource_options(system_id, module_id):
        options = []
        total_interface_count = 0
        sql = """select id,res_name from crud_resource where system_id='%s' and module_id='%s' order by res_name""" % (
            system_id, module_id)
        rows = db_util.fetchall(sql)
        for row in rows:
            sql = """select count(*) from crud_interface where resource_id='%s'""" % row[0]
            interface_count = db_util.fetchone(sql)[0]
            total_interface_count += interface_count
            option = {"value": row[0], "label": row[1], "icon": "fas fa-tasks", "system_id": system_id,
                      "module_id": module_id, "resource_id": row[0], "children_count": interface_count}
            options.append(option)

        return options, total_interface_count

    @staticmethod
    def module_options(system_id):
        options = []
        total_resource_count = 0
        total_interface_count = 0
        sql = """select id,m_name from crud_module  where system_id='%s' order by m_name""" % system_id
        rows = db_util.fetchall(sql)
        for row in rows:
            children, interface_count = TreeHandler.resource_options(system_id, row[0])
            total_resource_count += len(children)
            total_interface_count += interface_count
            option = {"value": row[0], "label": row[1], "icon": "fa fa-folder", "system_id": system_id,
                      "module_id": row[0], "resource_id": None, "children": children,
                      "children_count": "%d/%d" % (len(children), interface_count)}
            options.append(option)
        return options, total_resource_count, total_interface_count

    def get(self):
        sql = """select id,sys_name from crud_system  where sys_status=1 order by sys_name"""
        rows = db_util.fetchall(sql)
        options = []
        for row in rows:
            children, resource_count, interface_count = TreeHandler.module_options(row[0])
            option = {"value": row[0], "label": row[1], "icon": "fa fa-sitemap", "system_id": row[0], "module_id": None,
                      "resource_id": None, "children": children,
                      "children_count": "%d/%d/%d" % (len(children), resource_count, interface_count)}
            options.append(option)
        res = {"status": 0, "message": "ok", "data": {"options": options}}

        self.set_response(res, 200)


class AddHandler(base_handler.BaseHandler):
    def post(self):
        logging.info(self.request.body.decode())
        req_body = json.loads(self.request.body.decode())
        sys_id = req_body.get("id", None)
        sys_code = req_body.get("sys_code", None)
        sys_name = req_body.get("sys_name", None)
        sys_leader = req_body.get("sys_leader", None)
        sys_desc = req_body.get("sys_desc", None)

        sql = """insert into crud_system (id,sys_code,sys_name,sys_leader,sys_desc,sys_status)values(?,?,?,?,?,?)"""
        param = [sys_id, sys_code, sys_name, sys_leader, sys_desc, 1]

        flag = db_util.execute_update(sql, param)
        if flag is True:
            res = {"status": 0, "message": "添加成功"}
        else:
            res = {"status": 1, "message": "添加失败"}

        self.set_response(res, 200)


class EditHandler(base_handler.BaseHandler):
    def post(self):
        logging.info(self.request.body.decode())
        req_body = json.loads(self.request.body.decode())
        sys_id = req_body.get("id", None)
        param = []
        up_sql = ""
        for field_name in ("sys_code", "sys_name", "sys_leader", "sys_desc", "sys_status"):
            field_value = req_body.get(field_name, None)
            if field_value:
                param.append(field_value)
                up_sql += "%s=?," % field_name
        param.append(sys_id)
        sql = """update crud_system set %s where id=?""" % (up_sql[0:-1])
        flag = db_util.execute_update(sql, param)
        if flag is True:
            res = {"status": 0, "message": "修改成功"}
        else:
            res = {"status": 1, "message": "修改失败"}

        self.set_response(res, 200)


class DelHandler(base_handler.BaseHandler):
    def post(self):
        logging.info(self.request.body.decode())
        req_body = json.loads(self.request.body.decode())
        flag = False
        sys_id = req_body.get("id", None)

        if sys_id and sys_id != "":
            sql = "delete from crud_system where id =?"
            db_util.execute_update(sql, [sys_id])
            sql = "delete from crud_database where system_id =?"
            db_util.execute_update(sql, [sys_id])
            sql = "delete from crud_module where system_id =?"
            db_util.execute_update(sql, [sys_id])
            sql = "delete from crud_resource where system_id=?"
            db_util.execute_update(sql, [sys_id])

            sql = "delete from crud_verify where interface_id in (select id from crud_interface where system_id =?)"
            flag = db_util.execute_update(sql, [sys_id])

            sql = "delete from crud_interface where system_id=?"
            db_util.execute_update(sql, [sys_id])

        if flag is True:
            res = {"status": 0, "message": "删除成功"}
        else:
            res = {"status": 1, "message": "删除失败"}

        self.set_response(res, 200)


class ExportHandler(base_handler.BaseHandler):
    def get(self):
        system_id = self.get_query_argument("id", None)

        res = {}
        sql = "select * from crud_system where id =?"
        rows = db_util.fetchall_dict(sql, [system_id])
        if len(rows) == 0:
            self.set_response("404", 404)
            return
        res["system"] = rows[0]
        system_code = rows[0]["sys_code"]
        system_name = rows[0]["sys_name"]

        file_name = "%s_%s.json" % (system_code, datetime.datetime.now().strftime("%Y%m%d%H%M%S"))

        sql = "select * from crud_database where system_id =?"
        rows = db_util.fetchall_dict(sql, [system_id])
        res["database"] = rows

        sql = "select * from crud_module where system_id =?"
        rows = db_util.fetchall_dict(sql, [system_id])
        res["module"] = rows

        sql = "select * from crud_resource where system_id =?"
        rows = db_util.fetchall_dict(sql, [system_id])
        res["resource"] = rows

        sql = "select * from crud_interface where system_id =?"
        rows = db_util.fetchall_dict(sql, [system_id])
        res["interface"] = rows

        sql = "select * from crud_verify where interface_id in (select id from crud_interface where system_id =?)"
        rows = db_util.fetchall_dict(sql, [system_id])
        res["verify"] = rows

        res_body = json.dumps(res, ensure_ascii=False)
        fd = open(f"static/export/{file_name}", "w")
        fd.write(res_body)
        fd.close()

        self.set_status(200)
        self.set_header('Content-Type', 'text/plain')
        self.set_header('Content-Disposition', 'attachment; filename=' + file_name)
        self.write(res_body.encode())
        self.finish()


class ImportHandler(base_handler.BaseHandler):
    def addRecord(self, table_name, row):
        sql = "delete from %s where id =?" % table_name
        db_util.execute_update(sql, [row["id"]])
        sql = "insert into %s(%s)values(%s)" % (table_name, ",".join(row.keys()), ",".join("?" * len(row)))
        param = list(row.values())
        db_util.execute_update(sql, param)

    def post(self):
        res = {"status": 0, "message": "success"}
        try:
            req_body = json.loads(self.request.body.decode())
            input_file = req_body.get("input_file", None)
            system_body = json.loads(base64.b64decode(input_file.split(";base64,")[1].encode()).decode())

            # 导入系统
            self.addRecord("crud_system", system_body["system"])

            for row in system_body["database"]:
                self.addRecord("crud_database", row)
            for row in system_body["module"]:
                self.addRecord("crud_module", row)
            for row in system_body["resource"]:
                self.addRecord("crud_resource", row)
            for row in system_body["interface"]:
                self.addRecord("crud_interface", row)
            for row in system_body["verify"]:
                self.addRecord("crud_verify", row)

        except Exception as ex:
            res = {"status": 1, "message": str(ex)}

        self.set_response(res, 200)
